#! /bin/bash
#SQOOP_HOME=/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/bin/sqoop
export LANG=zh_CN.UTF-8
SQOOP_HOME=/usr/bin/sqoop
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

echo '========================================'
echo '==============开始增量导入==============='
echo '========================================'

# 先删除昨天的dt分区数据,再导入昨天的数据
${HIVE_HOME} -S -e "
ALTER TABLE hz03_dlp_zx_ods.customer DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.customer_appeal DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.customer_clue DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.customer_relationship DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.employee DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.itcast_school DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.itcast_subject DROP IF EXISTS PARTITION (dt='${TD_DATE}');
ALTER TABLE hz03_dlp_zx_ods.scrm_department DROP IF EXISTS PARTITION (dt='${TD_DATE}');
"
# 全量不变的表不用再导

# 增量表, 先删除dfs中的分区文件建
/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/customer/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from customer where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table customer \
-m 1
wait

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/customer_appeal/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from customer_appeal where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table customer_appeal \
-m 1
wait

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/customer_clue/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select
  id, 
  create_date_time, 
  update_date_time, 
  deleted, 
  customer_id, 
  customer_relationship_id, 
  session_id, 
  sid, 
  status,
  user as users, 
  create_time, 
  platform, 
  s_name, 
  seo_source, 
  seo_keywords, 
  ip, 
  referrer, 
  from_url, 
  landing_page_url, 
  url_title, 
  to_peer, 
  manual_time, 
  begin_time, 
  reply_msg_count, 
  total_msg_count, 
  msg_count, 
  comment, 
  finish_reason, 
  finish_user, 
  end_time, 
  platform_description, 
  browser_name, 
  os_info, 
  area, 
  country, 
  province, 
  city, 
  creator, 
  name, 
  idcard, 
  phone, 
  itcast_school_id, 
  itcast_school, 
  itcast_subject_id, 
  itcast_subject, 
  wechat, 
  qq, 
  email, 
  gender, 
  level, 
  origin_type, 
  information_way, 
  working_years, 
  technical_directions, 
  customer_state, 
  valid, 
  anticipat_signup_date, 
  clue_state, 
  scrm_department_id, 
  superior_url, 
  superior_source, 
  landing_url, 
  landing_source, 
  info_url, 
  info_source, 
  origin_channel, 
  course_id, 
  course_name, 
  zhuge_session_id, 
  is_repeat, 
  tenant, 
  activity_id, 
  activity_name, 
  follow_type,
  shunt_mode_id, 
  shunt_employee_group_id, '${TD_DATE}' as dt from customer_clue where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table customer_clue \
-m 1
wait

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/customer_relationship/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from customer_relationship where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table customer_relationship \
-m 1
wait

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/employee/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from employee where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table employee \
-m 1
wait

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/itcast_school/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from itcast_school where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table itcast_school \
-m 1
wait

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/itcast_subject/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from itcast_subject where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table itcast_subject \
-m 1
wait 

/usr/bin/hdfs dfs -rmr /user/hive/warehouse/hz03_dlp_zx_ods.db/scrm_department/dt=${TD_DATE}
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--driver 'com.mysql.jdbc.Driver' \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username hz_class03 \
--password hz_class03_567 \
--query "select *, '${TD_DATE}' as dt from scrm_department where ((create_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_date_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59')) and \$CONDITIONS" \
--hcatalog-database hz03_dlp_zx_ods \
--hcatalog-table scrm_department \
-m 1
wait

echo '========================================'
echo '=================success==============='
echo '========================================'
 